import pandas as pd
import pymysql
import uuid
import datetime
import random
import pinyin.cedict
import json
from urllib import request
import time

db = pymysql.connect( "10.2.5.100", "root", "Kc@123456", "helmet_operate_sit" )
cursor = db.cursor()


def getUUidTime():
    list_num = [1, 2, 3, 4, 5, 6, 7, 8, 9, 0]
    veri_str = random.sample( list_num, 2 )
    veri_num = random.sample( list_num, 2 )
    veri_out = random.sample( veri_num + veri_str, 4 )
    veri_res = str( veri_out[0] ) + str( veri_out[1] ) + str( veri_out[2] ) + str( veri_out[3] )
    nowTime = datetime.datetime.now().strftime( "%Y%m%d%H%M%S%f" )
    uniqueNum = str( nowTime ) + str( veri_res )
    return uniqueNum


def savedata(saveSql):
    for sql in saveSql:
        cursor.execute( sql )
        db.commit()


def getSqlData(sql):

    cursor.execute( sql )
    data = cursor.fetchone()
    if data == None:
        return ''
    return data[0]


def typeAdd(typeName):
    add_path = 'http://183.62.134.116:8800/base/data/addUpdateDataDictionary'
    pinType = pinyin.get( typeName, format="strip" )
    header = {'Content-Type': 'application/json',
              'trustToken': '93978ffb50540258b76230b654fa50d58a0bf380'}
    data_json = {
        "objValue1": "",
        "objName": typeName,
        "objCode": pinType,
        "orderNum": 0,
        "objParentCode": "faultType",
        "objParentId": "8c7e9779-47b3-4717-83a9-3185c0cd4d0c"
    }
    request_request = request.Request( add_path, json.dumps( data_json ).encode( 'utf-8' ), header )
    request.urlopen( request_request )


def getData(data):
    excel_data = []
    # 获取最终客户id
    customerId = data[11]
    data_ = data[0]
    customer_id_sql = 'SELECT customer_id FROM kc_customer_info WHERE order_id = (SELECT order_id FROM kc_order_info WHERE contract_number = \'{0}\') AND is_original = \'1\' AND customer_name = \'{1}\''.format(
        str( data_ ).strip(), str( customerId ).strip() )

    cst = getSqlData( customer_id_sql )
    if cst == '':
        ddd = 'SELECT order_id FROM kc_order_info WHERE contract_number = \'{0}\''.format(str( data_ ).strip())
        sql_data = getSqlData( ddd )
        customer_name_sql = 'SELECT customer_id FROM kc_customer_info WHERE customer_name = \'{0}\' AND order_id = \'{1}\' '.format( str( data[1] ).strip(),sql_data)
        cst = getSqlData( customer_name_sql )
    excel_data.append( cst )

    # 获取问题类型
    typeName = data[6]
    type_sql = 'SELECT count(1)FROM kc_data_dictionary_t WHERE obj_name = \'{0}\' and delete_flag = \'{1}\''.format(
        str( typeName ).strip(), '0' )
    cursor.execute( type_sql )
    type_count = cursor.fetchone()
    count_ = type_count[0]
    if count_ <= 0:
        typeAdd( typeName )

    if typeName == str( "\\" ):
        typeName = '其它'
    problem_type_sql = 'SELECT id FROM kc_data_dictionary_t WHERE obj_name = \'{0}\' and delete_flag = \'{1}\''.format(
        str( typeName ).strip(), '0' )
    prodata = getSqlData( problem_type_sql )
    if prodata == '':
        print( prodata )
    excel_data.append( prodata )

    # 获取运维人员id
    opsUserNames = data[2]
    splce = str( opsUserNames ).split( "、" )
    opsUserName = splce[0]
    ops_user_sql = 'SELECT user_id FROM sys_user WHERE user_realname like  \'%{0}%\''.format(
        str( opsUserName ).strip() )
    udata = getSqlData( ops_user_sql )
    excel_data.append( udata )

    orderName = data[0]
    order_sql = 'SELECT order_id FROM kc_order_info WHERE contract_number = \'{0}\''.format( str( orderName ).strip() )
    orders = getSqlData( order_sql )
    excel_data.append( orders )

    return excel_data


if __name__ == '__main__':
    excel = pd.read_excel( "/Users/xuyihang/Downloads/副本副本运维报表0917.xlsx",
                           usecols=[0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10,11], names=None)
    excel_data = excel.values.tolist()
    quality_sql_list = []
    dail_sql_list = []
    for excel_ in excel_data:
        flag = '0'
        if excel_[8] == '关闭':
            flag = '0'
        else:
            flag = '1'

        if pd.isnull( excel_[9] ):
            excel_[9] = excel_[5]
        if pd.isnull( excel_[2] ):
            excel_[2] = "詹建明"
        if pd.isnull( excel_[4] ):
            excel_[4] = ''
        if pd.isnull( excel_[5] ):
            excel_[5] = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
            print(excel_[5])
        data = getData( excel_ )
        uuid_ = uuid.uuid1()
        timeId = getUUidTime()
        add_quality_sql = 'INSERT INTO  kc_device_quality_fault_t (id, device_id, fault_id,problem_type, problem_description, ops_user,ops_state, find_date, to_quality_flag, order_id, customer_id,fault_number)' \
                          ' VALUES (uuid(),\'{0}\',\'{1}\',\'{2}\',\'{3}\',\'{4}\',\'{5}\',\'{6}\',\'1\',\'{7}\',\'{8}\',\'{9}\')' \
            .format( excel_[4], uuid_, data[1], excel_[7], data[2], flag, excel_[5], data[3], data[0], timeId )
        quality_sql_list.append( add_quality_sql )

        add_dail_sql = ' insert into kc_device_daily_progress_t (id, fault_id, report_date,report_content) values (uuid(),\'{0}\',\'{1}\',\'{2}\')' \
            .format( uuid_, excel_[9], excel_[10] )

        dail_sql_list.append( add_dail_sql )

    savedata( quality_sql_list )
    savedata( dail_sql_list )
